{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "8b2efe96-b39a-4edb-8672-555c29702743",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>block_id</th>\n",
       "      <th>transaction_hash</th>\n",
       "      <th>time</th>\n",
       "      <th>token_address</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_symbol</th>\n",
       "      <th>token_decimals</th>\n",
       "      <th>sender</th>\n",
       "      <th>recipient</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19000000</td>\n",
       "      <td>595fa89f9a51033a5e2d2e26fec531a126ac0218903df7...</td>\n",
       "      <td>2024-01-13 19:17:23</td>\n",
       "      <td>0xdAC17F958D2ee523a2206206994597C13D831ec7</td>\n",
       "      <td>Tether USD</td>\n",
       "      <td>USDT</td>\n",
       "      <td>6</td>\n",
       "      <td>0x0afad7f20d337943c51c4afb122a3507ca12a691</td>\n",
       "      <td>0xacdeaea45ee1133a1cb59c2837b3904d1052ca08</td>\n",
       "      <td>140033687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19000000</td>\n",
       "      <td>4b2a83dfc74dc270d55b9bd450571c44a93b42c34c9101...</td>\n",
       "      <td>2024-01-13 19:17:23</td>\n",
       "      <td>0xdAC17F958D2ee523a2206206994597C13D831ec7</td>\n",
       "      <td>Tether USD</td>\n",
       "      <td>USDT</td>\n",
       "      <td>6</td>\n",
       "      <td>0xf89d7b9c864f589bbf53a82105107622b35eaa40</td>\n",
       "      <td>0x76e1204e5b153d67b66bfd8c3b8e05183273d15e</td>\n",
       "      <td>310000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19000000</td>\n",
       "      <td>e5ed2d297ead627e43e62494c7e5d24aab046cefa51c50...</td>\n",
       "      <td>2024-01-13 19:17:23</td>\n",
       "      <td>0xdAC17F958D2ee523a2206206994597C13D831ec7</td>\n",
       "      <td>Tether USD</td>\n",
       "      <td>USDT</td>\n",
       "      <td>6</td>\n",
       "      <td>0x21a31ee1afc51d94c2efccaa2092ad1028285549</td>\n",
       "      <td>0x6a1898cb6db8a2e214de03c439fd7d11961bfde4</td>\n",
       "      <td>55000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19000000</td>\n",
       "      <td>144efa458e552478a601329607931bd96462caa2dfcdc4...</td>\n",
       "      <td>2024-01-13 19:17:23</td>\n",
       "      <td>0xdAC17F958D2ee523a2206206994597C13D831ec7</td>\n",
       "      <td>Tether USD</td>\n",
       "      <td>USDT</td>\n",
       "      <td>6</td>\n",
       "      <td>0x9696f59e4d72e237be84ffd425dcad154bf96976</td>\n",
       "      <td>0x9e4d93a17fe443fff3b04c7bca732b34b1180c38</td>\n",
       "      <td>3130285500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19000000</td>\n",
       "      <td>81a353f8643ab2dd03a848d128b3d65fa9fb57c8c20170...</td>\n",
       "      <td>2024-01-13 19:17:23</td>\n",
       "      <td>0xdAC17F958D2ee523a2206206994597C13D831ec7</td>\n",
       "      <td>Tether USD</td>\n",
       "      <td>USDT</td>\n",
       "      <td>6</td>\n",
       "      <td>0x867a4aad7185082ffc3b4ac8a8357887ebaebff1</td>\n",
       "      <td>0x4cc3486a37fea1c97071292116d7e374584e4482</td>\n",
       "      <td>1147415829</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   block_id                                   transaction_hash  \\\n",
       "0  19000000  595fa89f9a51033a5e2d2e26fec531a126ac0218903df7...   \n",
       "1  19000000  4b2a83dfc74dc270d55b9bd450571c44a93b42c34c9101...   \n",
       "2  19000000  e5ed2d297ead627e43e62494c7e5d24aab046cefa51c50...   \n",
       "3  19000000  144efa458e552478a601329607931bd96462caa2dfcdc4...   \n",
       "4  19000000  81a353f8643ab2dd03a848d128b3d65fa9fb57c8c20170...   \n",
       "\n",
       "                  time                               token_address  \\\n",
       "0  2024-01-13 19:17:23  0xdAC17F958D2ee523a2206206994597C13D831ec7   \n",
       "1  2024-01-13 19:17:23  0xdAC17F958D2ee523a2206206994597C13D831ec7   \n",
       "2  2024-01-13 19:17:23  0xdAC17F958D2ee523a2206206994597C13D831ec7   \n",
       "3  2024-01-13 19:17:23  0xdAC17F958D2ee523a2206206994597C13D831ec7   \n",
       "4  2024-01-13 19:17:23  0xdAC17F958D2ee523a2206206994597C13D831ec7   \n",
       "\n",
       "   token_name token_symbol  token_decimals  \\\n",
       "0  Tether USD         USDT               6   \n",
       "1  Tether USD         USDT               6   \n",
       "2  Tether USD         USDT               6   \n",
       "3  Tether USD         USDT               6   \n",
       "4  Tether USD         USDT               6   \n",
       "\n",
       "                                       sender  \\\n",
       "0  0x0afad7f20d337943c51c4afb122a3507ca12a691   \n",
       "1  0xf89d7b9c864f589bbf53a82105107622b35eaa40   \n",
       "2  0x21a31ee1afc51d94c2efccaa2092ad1028285549   \n",
       "3  0x9696f59e4d72e237be84ffd425dcad154bf96976   \n",
       "4  0x867a4aad7185082ffc3b4ac8a8357887ebaebff1   \n",
       "\n",
       "                                    recipient       value  \n",
       "0  0xacdeaea45ee1133a1cb59c2837b3904d1052ca08   140033687  \n",
       "1  0x76e1204e5b153d67b66bfd8c3b8e05183273d15e   310000000  \n",
       "2  0x6a1898cb6db8a2e214de03c439fd7d11961bfde4    55000000  \n",
       "3  0x9e4d93a17fe443fff3b04c7bca732b34b1180c38  3130285500  \n",
       "4  0x4cc3486a37fea1c97071292116d7e374584e4482  1147415829  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# USE THIS CODE\n",
    "from web3 import Web3\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "# Replace with your actual Infura Project ID\n",
    "INFURA_PROJECT_ID = \"<TODO-replace>\"\n",
    "INFURA_URL = f\"https://mainnet.infura.io/v3/{INFURA_PROJECT_ID}\"\n",
    "\n",
    "# Connect to Infura node\n",
    "w3 = Web3(Web3.HTTPProvider(INFURA_URL))\n",
    "\n",
    "# ERC-20 Transfer event signature\n",
    "TRANSFER_TOPIC = \"0x\" + w3.keccak(text=\"Transfer(address,address,uint256)\").hex()\n",
    "\n",
    "# Example with a given Block range and token contract, here showing for USDT\n",
    "# replace the contract address and block range as desired. \n",
    "START_BLOCK = 19000000\n",
    "END_BLOCK = 19000005\n",
    "CONTRACT_ADDRESS = \"0xdAC17F958D2ee523a2206206994597C13D831ec7\"\n",
    "\n",
    "records = []\n",
    "block_times = {}\n",
    "token_metadata = {}\n",
    "\n",
    "def get_block_timestamp(block_number):\n",
    "    if block_number not in block_times:\n",
    "        blk = w3.eth.get_block(block_number)\n",
    "        block_times[block_number] = datetime.utcfromtimestamp(blk.timestamp).strftime('%Y-%m-%d %H:%M:%S')\n",
    "    return block_times[block_number]\n",
    "\n",
    "def get_token_info(address, fn_sig, decode_type='string'):\n",
    "    try:\n",
    "        result = w3.eth.call({\n",
    "            \"to\": address,\n",
    "            \"data\": fn_sig\n",
    "        })\n",
    "        if decode_type == 'string':\n",
    "            if len(result) > 64:\n",
    "                stripped = result[64:]\n",
    "                return stripped.rstrip(b'\\x00').decode('utf-8', errors='replace')\n",
    "            else:\n",
    "                return result.rstrip(b'\\x00').decode('utf-8', errors='replace')\n",
    "        elif decode_type == 'int':\n",
    "            return int.from_bytes(result, byteorder='big')\n",
    "    except Exception:\n",
    "        return None\n",
    "\n",
    "# enrich data with token metadata: symbol, decimal, name\n",
    "def get_token_metadata(address):\n",
    "    if address in token_metadata:\n",
    "        return token_metadata[address]\n",
    "    name = get_token_info(address, \"0x06fdde03\", 'string')\n",
    "    symbol = get_token_info(address, \"0x95d89b41\", 'string')\n",
    "    decimals = get_token_info(address, \"0x313ce567\", 'int')\n",
    "    token_metadata[address] = (name, symbol, decimals)\n",
    "    return token_metadata[address]\n",
    "\n",
    "# Query for logs, filter for Transfer events\n",
    "logs = w3.eth.get_logs({\n",
    "    \"fromBlock\": START_BLOCK,\n",
    "    \"toBlock\": END_BLOCK,\n",
    "    \"address\": CONTRACT_ADDRESS,\n",
    "    \"topics\": [TRANSFER_TOPIC]\n",
    "})\n",
    "\n",
    "# Process logs\n",
    "for log in logs:\n",
    "    token = log['address']\n",
    "    block = log['blockNumber']\n",
    "    tx_hash = log['transactionHash'].hex()\n",
    "    time = get_block_timestamp(block)\n",
    "    sender = \"0x\" + log['topics'][1].hex()[-40:]\n",
    "    recipient = \"0x\" + log['topics'][2].hex()[-40:]\n",
    "    value = int.from_bytes(log['data'], byteorder='big')\n",
    "\n",
    "    name, symbol, decimals = get_token_metadata(token)\n",
    "\n",
    "    records.append({\n",
    "        \"block_id\": block,\n",
    "        \"transaction_hash\": tx_hash,\n",
    "        \"time\": time,\n",
    "        \"token_address\": token,\n",
    "        \"token_name\": name,\n",
    "        \"token_symbol\": symbol,\n",
    "        \"token_decimals\": decimals,\n",
    "        \"sender\": sender,\n",
    "        \"recipient\": recipient,\n",
    "        \"value\": value\n",
    "    })\n",
    "\n",
    "df = pd.DataFrame(records)\n",
    "df.head()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.20"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
